# coding=utf-8
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl import Workbook, load_workbook


class BaseExcel:
    """
    提供了对excel工作表进行 基础样式设置、基础数据写入、自动列宽计算和标题列筛选 的功能
    """

    def __init__(self, path=None,auto_width=True,auto_filter=True):
        self.wb = None  # 工作簿
        self.ws = None  # 工作表
        self.auto_width = auto_width  # 自动列宽
        self.auto_filter = auto_filter     # 自动筛选
        self.width_list = []  # 列宽列表
        self.head = None  # 合并单元格的头，类型为字符串，样式为16号字、加粗、居中、黑色实心边框
        self.__title = []  # 标题列数据，类型为列表，样式为黑体、居中、黑色实心边框
        self.__data = []  # 正文数据，类型为二维列表，样式为居中、黑色实心边框
        self.__result = []  # 结果列，类型为二维列表，样式为黄色底纹、居中、黑色实心边框
        self.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)  # 水平居中、垂直居中、自动换行
        self.side = Side(style='thin', color='000000')  # 黑色实线边框
        self.border = Border(bottom=self.side, right=self.side, left=self.side, top=self.side)
        self.result_fill = PatternFill(fill_type='solid', start_color='FFFF00')  # 黄色底纹
        if path is None:
            self.wb = Workbook()  # 新建工作簿
            self.ws = self.wb.active  # 获取工作表
        else:
            self.read(path)

    def read(self, path):
        """
        读取excel
        :param header: 标题所在列，1代表第一行，0代表没有
        :param path: 文件路径
        :return:
        """
        self.wb = load_workbook(path)
        self.ws = self.wb.active
        values = self.ws.values
        self.title = values[0]
        for row in values[1:]:
            self.append(list(row))
        return self

    @property
    def title(self):
        return self.__title

    @title.setter
    def title(self, value):
        self.__title = value
        self.__width__handle(value)

    @property
    def data(self):
        return self.__data

    @data.setter
    def data(self, value):
        for item in value:
            self.append(item)
        return self

    @property
    def result(self):
        return self.__result

    @result.setter
    def result(self, value):
        for item in value:
            self.append(item, result=True)
        return self

    def append(self, value, result=False):
        """
        添加单行数据
        :param value:
        :param result: 是否属于结果列
        :return: self
        """
        if result:
            self.__result.append(value)
        else:
            self.__data.append(value)
        self.__width__handle(value)
        return self

    def clear(self):
        """
            清空数据
        """
        self.__data = []


    def __width__handle(self, value):
        """
        宽度处理
        :param value:写入的数据
        :return:
        """
        if self.auto_width:
            if len(self.width_list) == 0:
                for item in value:
                    self.width_list.append(self.__get_width(item))  # 初始化
            else:
                for index, item in enumerate(value):
                    width = self.__get_width(item)
                    if self.width_list[index] < width:  # 通过比较获得最大列宽
                        self.width_list[index] = width

    # @staticmethod
    def __get_width(self, value, min_width=9, max_width=50):
        """
        得到列宽
        计算方式：利用GBK编码方式，非汉字字符占1个长度，汉字字符占2个长度
        :param value:
        :param min_width: 最小列宽
        :param max_width: 最大列宽
        :return:
        """
        width = len(str(value).encode("GBK")) + 1  # +1既可以补充误差又可以让两边留有一定的空白，美观
        if width < min_width:
            width = min_width
        if width > max_width:
            width = max_width
        return width

    def save(self, path):
        """
        写入excel
        :return:
        """
        if len(self.__title) == 0:
            raise Exception("标题列未设置")
        next_row = 1  # 行的游标
        """设置合并单元格的头"""
        if self.head is not None:
            head_range = 'A1:{}1'.format(get_column_letter(len(self.__title)))
            head_cell = self.ws[head_range][0]
            for item in head_cell:
                item.border = self.border
            self.ws.merge_cells(head_range)
            a1 = self.ws["A1"]
            a1.value = self.head
            a1.font = Font(size=16, bold=True)
            a1.alignment = self.alignment
            next_row += 1
        """设置标题"""
        if self.auto_filter:
            auto_filter_range = "A{}:{}{}".format(next_row, get_column_letter(len(self.title)), next_row)
            self.ws.auto_filter.ref = auto_filter_range  # 设置标题行筛选
        for index, item in enumerate(self.title):
            cell = self.ws.cell(next_row, index + 1)
            cell.value = item
            cell.font = Font(name="黑体", size=12)
            cell.border = self.border
            cell.alignment = self.alignment
        next_row += 1
        """设置正文数据"""
        for row in self.__data:
            for index, item in enumerate(row):
                cell = self.ws.cell(next_row, index + 1)
                cell.value = item
                cell.border = self.border
                cell.alignment = self.alignment
            next_row += 1
        """设置结果数据"""
        for row in self.__result:
            for index, item in enumerate(row):
                cell = self.ws.cell(next_row, index + 1)
                cell.value = item
                cell.border = self.border
                cell.alignment = self.alignment
                cell.fill = self.result_fill
            next_row += 1
        """设置列宽"""
        if self.auto_width:
            for index, item in enumerate(self.width_list):
                self.ws.column_dimensions[get_column_letter(index + 1)].width = item
        self.wb.save(path)
        return self



    def __str__(self):
        value = ""
        if self.head is not None:
            value = value + self.head + "\n"
        value = value + str(self.title) + "\n"
        for item in self.data:
            value = value + str(item) + "\n"
        if len(self.result) > 0:
            for item in self.result:
                value = value + str(item) + "\n"
        return value


"""测试代码"""
if __name__ == "__main__":
    e = BaseExcel()
    e.head = "学生信息收集表"
    e.title = ['姓名', '学号', '年龄']
    e.append(['张三', "1101", 17])
    e.append(['李四', "1102", 18])
    e.append(['王五', "1103", 16])
    e.append(["", "合计", 3], result=True)
    e.save(r'测试.xlsx')  # 保存到指定路径
